﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CPPEI.Coolzon.Core.Repositories.SQL
{
     public class SqlVersionFileRepository: IVersionFileRepository
    {
         public bool AddVersionFile(DbContext context, Data.VersionFile versionFile)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "insert into VersionFile(Id,Name,LatestChangeset,LatestChangesetId,LatestChangesetUser,LatestChangesetDate,FullName,Extention) values (@Id,@Name,@LatestChangeset,@LatestChangesetId,@LatestChangesetUser,@LatestChangesetDate,@FullName,@Extention) ";
                    cmd.Parameters.Add(new SqlParameter("Id", versionFile.Id));
                    cmd.Parameters.Add(new SqlParameter("Name", versionFile.Name));
                    cmd.Parameters.Add(new SqlParameter("LatestChangeset", versionFile.LatestChangeset));
                    cmd.Parameters.Add(new SqlParameter("LatestChangesetId", versionFile.LatestChangesetId));
                    cmd.Parameters.Add(new SqlParameter("LatestChangesetUser", versionFile.LatestChangesetUser));
                    cmd.Parameters.Add(new SqlParameter("LatestChangesetDate", versionFile.LatestChangesetDate));
                    cmd.Parameters.Add(new SqlParameter("FullName", versionFile.FullName));
                    cmd.Parameters.Add(new SqlParameter("Extention", versionFile.Extention));
                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

         public bool UpdateVersionFileById(DbContext context, Data.VersionFile versionFile)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "update VersionFile set Name = @Name ,LatestChangeset = @LatestChangeset ,LatestChangesetId = @LatestChangesetId ,LatestChangesetUser = @LatestChangesetUser ,LatestChangesetDate = @LatestChangesetDate ,FullName = @FullName ,Extention = @Extention where Id=@Id )";
                    cmd.Parameters.Add(new SqlParameter("Id", versionFile.Id));
                    cmd.Parameters.Add(new SqlParameter("Name", versionFile.Name));
                    cmd.Parameters.Add(new SqlParameter("LatestChangeset", versionFile.LatestChangeset));
                    cmd.Parameters.Add(new SqlParameter("LatestChangesetId", versionFile.LatestChangesetId));
                    cmd.Parameters.Add(new SqlParameter("LatestChangesetUser", versionFile.LatestChangesetUser));
                    cmd.Parameters.Add(new SqlParameter("LatestChangesetDate", versionFile.LatestChangesetDate));
                    cmd.Parameters.Add(new SqlParameter("FullName", versionFile.FullName));
                    cmd.Parameters.Add(new SqlParameter("Extention", versionFile.Extention));
                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool DeleteVersionFileById(DbContext context, Guid Id)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "delete from VersionFile where Id=@Id ";
                    cmd.Parameters.Add(new SqlParameter("Id", Id));
                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public Data.VersionFile GetVersionFileById(DbContext context, Guid Id)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd))
                    {
                        cmd.Connection = (SqlConnection)context.DbConnection;
                        cmd.CommandText = "select Id, Name, LatestChangeset, LatestChangesetId, LatestChangesetUser, LatestChangesetDate, FullName, Extention from VersionFile where Id=@Id ";
                        cmd.Parameters.Add(new SqlParameter("Id", Id));

                        DataSet oDataSet = new DataSet();
                        sqlDataAdapter.Fill(oDataSet);

                        Data.VersionFile versionFile = new Data.VersionFile();
                        if (oDataSet.Tables[0].Rows.Count > 0)
                        {
                            byte[] dataByte = new byte[int.MaxValue];
                            versionFile.Id = new Guid(oDataSet.Tables[0].Rows[0]["Id"].ToString());
                            versionFile.Name = oDataSet.Tables[0].Rows[0]["Name"].ToString();
                            versionFile.LatestChangeset =Convert.ToDouble(oDataSet.Tables[0].Rows[0]["LatestChangeset"].ToString());
                            versionFile.LatestChangesetId = new Guid(oDataSet.Tables[0].Rows[0]["LatestChangesetId"].ToString());
                            versionFile.LatestChangesetUser = oDataSet.Tables[0].Rows[0]["LatestChangesetUser"].ToString();
                            versionFile.LatestChangesetDate = Convert.ToDateTime(oDataSet.Tables[0].Rows[0]["LatestChangesetDate"].ToString());
                            versionFile.FullName = oDataSet.Tables[0].Rows[0]["FullName"].ToString();
                            versionFile.Extention = oDataSet.Tables[0].Rows[0]["Extention"].ToString();
                            return versionFile;
                        }
                        else
                        {
                            return null;
                        }
                    }

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public PagedList<Data.VersionFile> GetVersionFileList(DbContext context, int start, int limit)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        cmd.Connection = (SqlConnection)context.DbConnection;
                        cmd.CommandText = "select * from (select *, ROW_NUMBER() OVER(ORDER BY Id) as rownum from [VersionFile]) a where a.rownum >= @paramStart and a.rownum <= @paramEnd";
                        cmd.Parameters.Add(new SqlParameter("paramStart", start));
                        cmd.Parameters.Add(new SqlParameter("paramEnd", start + limit));
                        DataSet ds = new DataSet();
                        sda.Fill(ds);
                        List<Data.VersionFile> versionFileList = null;
                        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        {
                            versionFileList = new List<Data.VersionFile>();
                            foreach (DataRow dr in ds.Tables[0].Rows)
                            {
                                Data.VersionFile versionFile = new Data.VersionFile();
                                versionFile.Id = new Guid(dr["Id"].ToString());
                                versionFile.Extention = dr["Extention"].ToString();
                                versionFile.FullName= dr["FullName"].ToString();
                                versionFile.LatestChangeset = Convert.ToDouble(dr["LatestChangeset"].ToString());
                                versionFile.LatestChangesetDate =Convert.ToDateTime(dr["LatestChangesetDate"].ToString());
                                versionFile.LatestChangesetId = new Guid(dr["LatestChangesetId"].ToString());
                                versionFile.LatestChangesetUser = dr["LatestChangesetUser"].ToString();
                                versionFile.Name = dr["Name"].ToString();
                              
                                versionFileList.Add(versionFile);
                            }
                            return new PagedList<Data.VersionFile>(versionFileList, start, limit, versionFileList.Count);
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool Exists(DbContext context,string fileFullName)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "select count(1) from VersionFile where FullName = @FullName  ";
                    cmd.Parameters.Add(new SqlParameter("FullName", fileFullName));
                  
                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
